//
//  SCDBManager.m
//  SCFoundationKit
//
//  Created by Chao Sheng on 2023/10/24.
//

#import "SCDBManager.h"
#import <FMDB/FMDB.h>
#import "SCBasicKit.h"

@interface SCDBManager ()

@property (nonatomic, copy) NSString *sqiltePath;

@end

@implementation SCDBManager

+ (instancetype)share {
    static SCDBManager * manager = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        manager = [[SCDBManager alloc] init];
        manager.sqiltePath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0] stringByAppendingPathComponent:@"scdb.sqlite"];
        NSLog(@"%@",manager.sqiltePath);
    });
    return manager;
}


- (void)createTableWithTableName:(NSString*)tableName
                 columnParams:(nonnull NSDictionary *)columnParams
                       
{
    FMDatabase *db = [FMDatabase databaseWithPath:self.sqiltePath];
    if ([db open]) { //如果没有数据库，会自动创建
        
        NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@(id INTEGER PRIMARY KEY AUTOINCREMENT, fieldNameParams);",tableName];
        NSString *fieldNameString = @"";
        for(NSString *key in columnParams.allKeys) {
            fieldNameString = [fieldNameString stringByAppendingString:[NSString stringWithFormat:@"%@ %@,",key,columnParams[key]]];
        }
        if (![fieldNameString sc_isEmpty]) {
            fieldNameString = [fieldNameString substringWithRange:NSMakeRange(0, fieldNameString.length-1)];
        }
        sql = [sql stringByReplacingOccurrencesOfString:@"fieldNameParams" withString:fieldNameString];
        BOOL success = [db executeUpdate:sql];
        NSLog(@"创建数据表%@，结果：%@，执行sql：%@",tableName,@(success),sql);
    }
    
    [db close];
}

- (void)insertTableWithTableName:(NSString *)tableName
                 columnParams:(nonnull NSDictionary *)columnParams {
    
    FMDatabase *db = [FMDatabase databaseWithPath:self.sqiltePath];
    if ([db open]) {
        NSString *sql = [NSString stringWithFormat:@"INSERT INTO %@(fieldNames) VALUES (fieldValues);",tableName];
        NSString *fieldNameString = @"";
        NSString *fieldValuesString = @"";
        for(NSString *key in columnParams.allKeys) {
            fieldNameString = [fieldNameString stringByAppendingString:[NSString stringWithFormat:@"%@,",key]];
            fieldValuesString = [fieldValuesString stringByAppendingString:[NSString stringWithFormat:@"'%@',",columnParams[key]]]; //vaule的值要用单引号括起来
        }
        if (![fieldNameString sc_isEmpty]) {
            fieldNameString = [fieldNameString substringWithRange:NSMakeRange(0, fieldNameString.length-1)];
        }
        if (![fieldValuesString sc_isEmpty]) {
            fieldValuesString = [fieldValuesString substringWithRange:NSMakeRange(0, fieldValuesString.length-1)];
        }
        sql = [sql stringByReplacingOccurrencesOfString:@"fieldNames" withString:fieldNameString];
        sql = [sql stringByReplacingOccurrencesOfString:@"fieldValues" withString:fieldValuesString];

        BOOL success = [db executeUpdate:sql];
        NSLog(@"插入数据表%@，结果：%@，执行sql：%@",tableName,@(success),sql);
    }
    
    [db close];
}

- (void)deleteTableWithTableName:(NSString *)tableName
                       columnName:(nonnull NSString *)columnName
                     columnValue:(nonnull id)columnVaule {
    
    FMDatabase *db = [FMDatabase databaseWithPath:self.sqiltePath];
    if ([db open]) {
        NSString *sql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ = '%@';",tableName,columnName,columnVaule];
        BOOL success = [db executeUpdate:sql];
        NSLog(@"删除数据表%@，结果：%@，执行sql：%@",tableName,@(success),sql);
    }
    [db close];
}

- (void)updateTableWithTableName:(NSString *)tableName
                       setColumn:(nonnull NSDictionary *)setColumn
                     whereColumn:(nonnull NSDictionary *)whereColumn {
    
    FMDatabase *db = [FMDatabase databaseWithPath:self.sqiltePath];
    if ([db open]) {
        NSString *sql = [NSString stringWithFormat:@"UPDATE %@ SET %@ = '%@' WHERE %@ = '%@';",tableName,setColumn.allKeys[0],setColumn.allValues[0],whereColumn.allKeys[0],whereColumn.allValues[0]];
        BOOL success = [db executeUpdate:sql];
        NSLog(@"更新数据表%@，结果：%@，执行sql：%@",tableName,@(success),sql);
    }
    [db close];
}

- (NSDictionary *)queryAllWithTableName:(NSString *)tableName
                             columnName:(NSString *)columnName
                            columnValue:(id)columnVaule {
    
    FMDatabase *db = [FMDatabase databaseWithPath:self.sqiltePath];
    NSMutableDictionary *dict = @{}.mutableCopy;
    if ([db open]) {
        NSString *sql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ = '%@';",tableName,columnName,columnVaule];
        FMResultSet *set = [db executeQuery:sql];
        if([set next]) {
            for(int i = 0; i < set.columnCount; i++) {
                NSString *columnName = [set columnNameForIndex:i];
                id vaule = [set objectForColumn:columnName];
                [dict setObject:vaule forKey:columnName];
            }
        }
        NSLog(@"查询数据表%@，执行sql：%@,结果：%@",tableName,sql,dict);
    }
    [db close];
    
    return dict;
}

@end
